# -*- coding: utf-8 -*-

'''
@author: Luís Fonte nº6043
@date 03/12/2012
@obs:
Reads .xls file with data and writes on database
'''

import xlrd
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy import Column, Date, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref
from sqlalchemy.orm import sessionmaker
from sqlalchemy import *

# xlrd fucntions seen on xlrd documentation:
# https://secure.simplistix.co.uk/svn/xlrd/trunk/xlrd/doc/xlrd.html


# The metric used on the processing of database was based on this tutorial of sqlalchemy
# http://www.blog.pythonlibrary.org/2012/07/01/a-simple-sqlalchemy-0-7-0-8-tutorial/


engine = create_engine('sqlite:///inscritos.db', echo=False) # To not print anything on shell
Base = declarative_base()

"""
 Class Database
 Initializes table 'inscritos' and defines its columns
"""
class Database(Base):
    __tablename__ = 'inscritos'

    id = Column(Integer, primary_key=True)# Table has to be an primary key with unique values
    Nome_do_Estabelecimento = Column(String) 
    Unidade_Organica = Column(String)
    Nivel_de_Formacao = Column(String)
    Curso_Superior = Column(String)
    Area_de_Formacao = Column(String)
    Ano_1995_96 = Column(String)
    Ano_1996_97 = Column(String)
    Ano_1997_98 = Column(String)
    Ano_1998_99 = Column(String)
    Ano_1999_00 = Column(String)
    Ano_2000_01 = Column(String)
    Ano_2001_02 = Column(String)
    Ano_2002_03 = Column(String)
    Ano_2003_04 = Column(String)
    Ano_2004_05 = Column(String)
    Ano_2005_06 = Column(String)
    Ano_2006_07 = Column(String)
    Ano_2007_08 = Column(String)
    Ano_2008_09 = Column(String)
    Ano_2009_10 = Column(String)
    Ano_2010_11 = Column(String)

    # Initialization of all columns of table inscritos
    def __init__(self,Nome_do_Estabelecimento, Unidade_Organica, Nivel_de_Formacao, Curso_Superior, Area_de_Formacao, Ano_1995_96,Ano_1996_97,Ano_1997_98,Ano_1998_99,Ano_1999_00,Ano_2000_01,Ano_2001_02,Ano_2002_03,Ano_2003_04,Ano_2004_05,Ano_2005_06,Ano_2006_07,Ano_2007_08,Ano_2008_09,Ano_2009_10,Ano_2010_11):
        self.Nome_do_Estabelecimento = Nome_do_Estabelecimento
        self.Unidade_Organica = Unidade_Organica
        self.Nivel_de_Formacao = Nivel_de_Formacao
        self.Curso_Superior = Curso_Superior
        self.Area_de_Formacao = Area_de_Formacao
        self.Ano_1995_96 = Ano_1995_96
        self.Ano_1996_97 = Ano_1996_97
        self.Ano_1997_98 = Ano_1997_98
        self.Ano_1998_99 = Ano_1998_99
        self.Ano_1999_00 = Ano_1999_00
        self.Ano_2000_01 = Ano_2000_01
        self.Ano_2001_02 = Ano_2001_02
        self.Ano_2002_03 = Ano_2002_03
        self.Ano_2003_04 = Ano_2003_04
        self.Ano_2004_05 = Ano_2004_05
        self.Ano_2005_06 = Ano_2005_06
        self.Ano_2006_07 = Ano_2006_07
        self.Ano_2007_08 = Ano_2007_08
        self.Ano_2008_09 = Ano_2008_09
        self.Ano_2009_10 = Ano_2009_10
        self.Ano_2010_11 = Ano_2010_11

        pass
    pass


################################################################################

"""
# Creates and returns a List with every cell filled with a formation name
# On that column on .xls file every formation name only appers one time on one row
# This function is used to fill the formation level column on the database
"""
def getColumnNamesToList(columnNumber):

    # Open .xls file
    try:
        file_xls = xlrd.open_workbook('Inscritos_2010-2011.xls')
    except IOError:
        print "Error reading xls file"
    file_xls.sheet_names()

    # Tabela 4.1 got the information that is needed
    dataFromSheet = file_xls.sheet_by_name(u'Tabela 4.1') #sheet 4.1

    # Creation of a List with four values, for append function start on 4th position, because of the format of .xls file
    FormationLevel_LIST = ['', '', '', '']

    # Verifies on every row if the cell is empty or not. If its not, keeps the value on a variable and appends it to the List
    for row in range(4, dataFromSheet.nrows):
        if dataFromSheet.cell_value(row, columnNumber) != '':
            value = dataFromSheet.cell_value(row, columnNumber)
            FormationLevel_LIST.append(value)
        else:
            FormationLevel_LIST.append(value)
            pass
        pass
    
    return FormationLevel_LIST



############################################################################



"""
 Function that fills database with data from .xls file
"""
def fillDatabase():
    Base.metadata.create_all(engine)
    # create a Session
    Session = sessionmaker(bind=engine)
    session = Session()
    # open xls file
    try:
        dataFromXLS = xlrd.open_workbook('Inscritos_2010-2011.xls')
    except:
        print "Error reading xls file"
    dataFromXLS.sheet_names()
    dataFromSheet = dataFromXLS.sheet_by_name(u'Tabela 4.1')
    # Gets those 3 lists with all cells filled
    EstablishmentNames_LIST = getColumnNamesToList(0)
    Unity_LIST = getColumnNamesToList(1)
    FormationLevel_LIST = getColumnNamesToList(2)

    # Puts data in order to be add to database
    for row in range(4, dataFromSheet.nrows):
        # Database object
        d = Database(EstablishmentNames_LIST[row],
                                Unity_LIST[row],
                                FormationLevel_LIST[row],
                                dataFromSheet.cell_value(row, 3),
                                dataFromSheet.cell_value(row, 4),
                                dataFromSheet.cell_value(row, 7),
                                dataFromSheet.cell_value(row, 10),
                                dataFromSheet.cell_value(row, 13),
                                dataFromSheet.cell_value(row, 16),
                                dataFromSheet.cell_value(row, 19),
                                dataFromSheet.cell_value(row, 22),
                                dataFromSheet.cell_value(row, 25),
                                dataFromSheet.cell_value(row, 28),
                                dataFromSheet.cell_value(row, 31),
                                dataFromSheet.cell_value(row, 34),
                                dataFromSheet.cell_value(row, 37),
                                dataFromSheet.cell_value(row, 40),
                                dataFromSheet.cell_value(row, 43),
                                dataFromSheet.cell_value(row, 46),
                                dataFromSheet.cell_value(row, 49),
                                dataFromSheet.cell_value(row, 52))

        session.add(d)  # adds data to database
        pass
    session.commit()
    pass
    
